package com.itstyle.quartz.service.impl;

import com.itstyle.quartz.dynamicquery.DynamicQuery;
import com.itstyle.quartz.job.kettleJob;
import com.itstyle.quartz.service.dao.ACLineDotService;
import com.itstyle.quartz.service.mapper.JobMapper;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;

/**
 * @author ycj
 * @version 1.0
 * @description
 * @date 2020/10/20 18:10
 */

@Service
//@Repository
@Transactional
public class ACLineDotServiceImpl implements ACLineDotService {

    private static final Logger logger = LoggerFactory.getLogger(kettleJob.class);

    @PersistenceContext
    private EntityManager em;

    public EntityManager getEntityManager() {
        return em;
    }

    @Autowired
    private DynamicQuery dynamicQuery;

    @Autowired
    private JobMapper jobMapper;

    @Autowired
    private JdbcTemplate jdbcTemplate;


    @Override
    public void save(HashMap<String, List<String>> read, String type,String cityname,String filename) {
        String tablename = "";
        String createtable = "";
        String name ="";
        String time = "";

        String A = "A";
        String B = "B";
        String C = "C";
        String X = "线";
        String Y = "有";
        String W = "无";

        //数据表标识
        int valueindex = 0;
        //表标识
        int tableindex = 0;
        //创建表标识
        int createindex = 0;
        int index = 0;
        int DTindex = 0;
        int dt_syindex = 0;

        //数据值
        List<String> values = read.get("value");
        //表
        List<String> tables = read.get("table");
        //表字段
        List<String> field = read.get("field");

        //获取文件时间
        String[] split1 = filename.split("_");
        if (type.equals("CIME")) {
            String[] split = split1[2].split("\\.");
            time=split[0];
        }else {
            time=split1[1];
        }

        logger.info("开始查找城市owner:" + cityname);

//        String findowner = "select owner from SG_DATACENTER.SG_ORG_DCC_B where name like '"+cityname+"%'";
        //DT文件设置对应的模式名，城市名
        if (type.equals("DT")) {
            if (cityname.equals("gl")) {
                cityname = "果洛";
                name = "GUOLUO_DT";
            } else if (cityname.equals("hb")) {
                cityname = "海北";
                name = "HAIBEI_DT";
            } else if (cityname.equals("hd")) {
                cityname = "海东";
                name = "HAIDONG_DT";
            } else if (cityname.equals("hh")) {
                cityname = "黄化";
                name = "HUANGHUA_DT";
            } else if (cityname.equals("hnz")) {
                cityname = "海南";
                name = "HAINAN_DT";
            } else if (cityname.equals("xn")) {
                cityname = "西宁";
                name = "XINING_DT";
            } else if (cityname.equals("hx")) {
                cityname = "海西";
                name = "HAIXI_DT";
            }
        }

        //获取所属地段id
        String owner = null;
        if (!type.equals("CIME")) {
            String cityname_1 = cityname+"%";
            List<String> list = jobMapper.findOwner(cityname_1);
            owner = list.get(0);
        }else {
            owner = "";
            name = "CIME";

        }

        logger.info("woner:"+owner);
        logger.info("设置模式名称:" + cityname);

        //设置模式名
        if (type.equals("QS")) {
            if (cityname.equals("青海")) {
                name = "QINGHAI";
            }else if (cityname.equals("西宁")){
                name = "XINING";
            }else if (cityname.equals("海南")) {
                name = "HAINAN";
            }else if (cityname.equals("海西")) {
                name = "HAIXI";
            }else if (cityname.equals("海北")) {
                name = "HAIBEI";
            }else if (cityname.equals("海东")) {
                name = "HAIDONG";
            }else if (cityname.equals("果洛")) {
                name = "GUOLUO";
            }else if (cityname.equals("黄化")) {
                name = "HUANGHUA";
            }
        }

        //遍历数据值
        for (int i = 0; i<values.size(); i++) {
                StringBuffer nativeSql = new StringBuffer();
                StringBuffer filedSql = new StringBuffer();
                StringBuffer buffer = new StringBuffer();
                String[] strings = null;
                int vlauesindex1 = 0;

                //获得一行数据
                String s = values.get(i);

                //对不标准数据格式化
                if (type.equals("QS") && tablename.equals("Compensator_P") && !tablename.equals("Island")) {
                    if (s.contains(" 青") && values.get(i).indexOf("青")>7) {
                        vlauesindex1 = values.get(i).indexOf("青");
                        s = s.substring(0,vlauesindex1-1)+s.substring(vlauesindex1,s.length());
                    }
                }

                if (type.equals("DT")) {
                    if (s.contains(" B")
                            || s.contains(" C")
                            || s.contains(" 有")
                            || s.contains(" 无")) {
                         if (s.contains(" B")) {
                            vlauesindex1 = values.get(i).indexOf(B);
                        }else if (s.contains(" C")) {
                            vlauesindex1 = values.get(i).indexOf(C);
                        }else if (s.contains(" 有")) {
                            vlauesindex1 = values.get(i).indexOf(Y);
                        }else if (s.contains(" 无")) {
                            vlauesindex1 = values.get(i).indexOf(W);
                        }
                        s = s.substring(0,vlauesindex1-1)+s.substring(vlauesindex1,s.length());
                    }

                     if (s.contains(". ")) {
                        int vlauesindex2 = s.indexOf(". ");
                        s = s.substring(0,vlauesindex2+1)+s.substring(vlauesindex2+2,s.length());
                    }else if (s.contains("/ ")) {
                         int vlauesindex3 = s.indexOf("/ ");
                         s = s.substring(0, vlauesindex3+1) + s.substring(vlauesindex3+2, s.length());
                     }else if (s.contains(" U")) {
                         int vlauesindex3 = s.indexOf("U");
                         s = s.substring(0, vlauesindex3-1) + s.substring(vlauesindex3, s.length());
                     }else if (s.contains("  线")) {
                         int vlauesindex3 = s.indexOf(X);
                         s = s.substring(0, vlauesindex3-2) + s.substring(vlauesindex3, s.length());
                     }else if (s.contains(" 线")) {
                        int vlauesindex3 = s.indexOf(" 线");
                        s = s.substring(0, vlauesindex3) + s.substring(vlauesindex3+1, s.length());
                     }else if (s.contains(" 功")) {
                         int vlauesindex3 = s.indexOf(" 功");
                         s = s.substring(0, vlauesindex3) + s.substring(vlauesindex3+1, s.length());
                     }else if (s.contains(" A") && !s.contains(" A海南")) {
                        int vlauesindex3 = values.get(i).indexOf(" A");
                         s = s.substring(0, vlauesindex3) + s.substring(vlauesindex3+1, s.length());
                     }else if (s.contains(" 目")) {
                         int vlauesindex3 = values.get(i).indexOf(" 目");
                         s = s.substring(0, vlauesindex3) + s.substring(vlauesindex3+1, s.length());
                     }else if (s.contains(" Ⅲ")) {
                         int vlauesindex3 = values.get(i).indexOf(" Ⅲ");
                         s = s.substring(0, vlauesindex3) + s.substring(vlauesindex3+1, s.length());
                     }else if (s.contains(" 母")) {
                         int vlauesindex3 = values.get(i).indexOf(" 母");
                         s = s.substring(0, vlauesindex3) + s.substring(vlauesindex3+1, s.length());
                     }else if (s.contains(" Ⅱ")) {
                         int vlauesindex3 = values.get(i).indexOf(" Ⅱ");
                         s = s.substring(0, vlauesindex3) + s.substring(vlauesindex3+1, s.length());
                     }else if (s.contains(" Ⅰ")) {
                         int vlauesindex3 = values.get(i).indexOf(" Ⅰ");
                         s = s.substring(0, vlauesindex3) + s.substring(vlauesindex3+1, s.length());
                     }

                     if (s.contains(" 电") && !s.contains("电容器131")) {
                         int vlauesindex3 = s.indexOf("电");
                         s = s.substring(0, vlauesindex3-1) + s.substring(vlauesindex3, s.length());
                     }
                }

                strings = s.split("\\s+");
                //标注每个表数据的第一位
                if (strings[1].equals("1") || strings[1].equals("201100")) {
                    valueindex++;
                }
                if (type.equals("DT") && strings[1].equals("0")) {
                    valueindex++;
                }
                //判断是否遍历到下张表数据
                if (valueindex>1) {
                    tableindex++;
                    valueindex=1;
                    createindex=0;
                }

                //查询表中是否有该表
                logger.info("开始查找模式中是否有该表:" + name);
                //获取表名
                tablename = tables.get(tableindex);
                String[] split = tablename.substring(1, tablename.length() - 1).split("::");
                tablename=split[0];

                //查找表是否存在
                String toUpperCase = tablename.toUpperCase();
                List<String> tablelist = jobMapper.findTable(name,toUpperCase);

                //拼接字段
                String fields = field.get(tableindex);
                String[] string = fields.split("\\s+");
                if (type.equals("CIME")) {
                    for (int j = 1; j < string.length; j++) {
                        if (j!=string.length-1) {
                            if (j==0) {
                                string[j] = string[j].substring(1);
                            }
                            if (tablename.equals("Substation")) {
                                if (string[j].equals("q") || string[j].equals("p")) {
                                    string[j]=string[j].toUpperCase();
                                }
                            }
                            filedSql.append("\""+string[j]+"\",");
                        }else {
                            filedSql.append("\""+string[j]+"\",\"owner\",\"f_time\"");
                        }
                    }
                }else {
                    for (int j = 2; j < string.length; j++) {
                        if (type.equals("DT") && cityname.equals("西宁")
                                || type.equals("DT") && cityname.equals("果洛")
                                || type.equals("DT") && cityname.equals("海北")
                                || type.equals("DT") && cityname.equals("海西")) {
                            if (string[0].equals("@")) {
                                valueindex++;
                            }
                        }
                        if (tablename.toUpperCase().equals("ACLINE")) {
                            if (!string[j].equals("J_nd") && !string[j].equals("I_nd") && index==1) {
                                index=0;
                            }
                            if (string[j].equals("I_nd")) {
                                index=index+1;
                                if (index==2){
                                    if (string[j].equals("I_nd")) {
                                        string[j]="I_bs";
                                    }
                                }
                            }
                            if (string[j].equals("J_nd")) {
                                if (index==2) {
                                    string[j]="J_bs";
                                    index=0;
                                }
                            }
                        }
                        if (type.equals("DT") && tablename.equals("Breaker")
                                || type.equals("DT") && tablename.equals("BusbarSection")
                                || type.equals("DT") && tablename.equals("Load")) {
                            if (string[j].equals("质量码")) {
                                DTindex++;
                                if (DTindex==2) {
                                    string[j]=string[j]+"_2";
                                }else if (DTindex==3) {
                                    string[j]=string[j]+"_3";
                                    DTindex=0;
                                }
                            }
                        }

                        if (type.equals("DT") && tablename.equals("SynchronousMachine")
                                || type.equals("DT") && tablename.equals("ACLineDot")
                                || type.equals("DT") && tablename.equals("TransformerWinding")) {
                            if (string[j].equals("质量码")) {
                                DTindex++;
                                if (DTindex==2) {
                                    string[j]=string[j]+"_2";
                                }else if (DTindex==3) {
                                    string[j]=string[j]+"_3";
                                }else if (DTindex==4) {
                                    string[j]=string[j]+"_4";
                                    DTindex=0;
                                }
                            }
                        }

                        if (type.equals("DT") && tablename.equals("SynchronousMachine") && cityname.equals("海南")
                        ||type.equals("DT") && tablename.equals("ACLineDot") && cityname.equals("海南")
                        ||type.equals("DT") && tablename.equals("TransformerWinding") && cityname.equals("海南")) {
                            if (j!=string.length-1) {
                                if (string[j].contains("质量码")) {
                                    dt_syindex++;
                                    if (dt_syindex==3) {
                                    }else {
                                        filedSql.append("\""+string[j]+"\",");
                                    }
                                }else {
                                    filedSql.append("\""+string[j]+"\",");
                                }
                            }else {
                                filedSql.append("\""+string[j]+"\",\"owner\",\"f_time\"");
                            }
                        } else if (type.equals("DT") && tablename.equals("Load") && cityname.equals("海南")) {
                            if (j!=string.length-1) {
                                    filedSql.append("\""+string[j]+"\",");
                            }else {
                                filedSql.append("\"owner\",\"f_time\"");
                            }
                        } else if (type.equals("DT") && tablename.equals("Breaker") && cityname.equals("海南")){
                            if (j!=string.length-1) {
                                filedSql.append("\""+string[j]+"\",");
                            }else {
                                filedSql.append("\"owner\",\"f_time\"");
                            }
                        }else {
                            if (j!=string.length-1) {
                                filedSql.append("\""+string[j]+"\",");
                            }else {
                                filedSql.append("\""+string[j]+"\",\"owner\",\"f_time\"");
                            }
                        }
                    }
                    dt_syindex=0;
                }

                //如果表示为0，并且没有查到该表存在，创建表
                if (createindex==0 && tablelist.size()==0) {
                    logger.info("创建表:" + tablename);

                    String createTableSQL = "CREATE TABLE "+name+"."+tablename.toUpperCase()+"(";
                    buffer.append(createTableSQL);

                    if (type.equals("CIME")) {
                        for (int a = 0; a < string.length; a++) {
                            if (a!=string.length-1) {
                                if (a==0) {
                                    buffer.append("\""+string[a].substring(1)+"\" INT IDENTITY(1, 1) NOT NULL,");
                                }else {
                                    buffer.append("\""+string[a]+"\" VARCHAR(200),");
                                }
                            } else {
                                buffer.append("\""+string[a]+"\" VARCHAR(200),\"owner\" VARCHAR(200),\"f_time\" VARCHAR(200))");
                            }
                        }
                    }else {
                        for (int a = 1; a < string.length; a++) {
                            if (a!=string.length-1) {
                                if (a==1) {
                                    buffer.append("\""+string[a]+"\" INT IDENTITY(1, 1) NOT NULL,");
                                }else {
                                    buffer.append("\""+string[a]+"\" VARCHAR(200),");
                                }
                            } else {
                                buffer.append("\""+string[a]+"\" VARCHAR(200),\"owner\" VARCHAR(200),\"f_time\" VARCHAR(200))");
                            }
                        }
                    }
                    logger.info("创建表语句:"+buffer.toString());
                    jdbcTemplate.execute(buffer.toString());
                    createindex=1;
                }

                logger.info("插入语句");
                //插入语句
                nativeSql.append("insert into "+name+"."+tablename.toUpperCase()+" ("+ filedSql +") VALUES (");
                for (int j = 2; j < strings.length; j++) {
                    if (type.equals("DT") && tablename.equals("system") && cityname.equals("海东")
                            || type.equals("DT") && tablename.equals("system") && cityname.equals("海南")
                            || type.equals("DT") && tablename.equals("system") && cityname.equals("黄化") ) {
                            if (j!=strings.length-1) {
                                nativeSql.append(strings[j]+",");
                            }else {
                                nativeSql.append(strings[strings.length-1]+",'"+owner+"','"+time+"');");
                            }
                    }else {
                        if (j!=strings.length-1) {
                            nativeSql.append("'"+strings[j]+"',");
                        }else {
                            nativeSql.append("'"+strings[strings.length-1]+"','"+owner+"','"+time+"');");
                        }
                    }
                }
                logger.info("插入语句:"+nativeSql.toString());
                jdbcTemplate.execute(nativeSql.toString());
        }
            //标注还原
            tableindex=0;
            valueindex=0;
            createindex=0;
    }

    @Override
    public void txtsave(List<String> tables, List<String> values, String type) {
        String tablename = "";
        String field = "";
        int valueindex = 0;
        int tableindex = 0;

        for (int i = 0; i<values.size(); i++) {
            StringBuffer nativeSql = new StringBuffer();
            String[] strings = null;
            strings = values.get(i).split("\\t");
            String substring = strings[0].substring(1);
            strings[0] = substring;

            if (strings[0].equals("1")) {
                valueindex++;
            }

            if (valueindex > 1) {
                tableindex++;
                valueindex = 1;
            }

            tablename = tables.get(tableindex);
            String[] split = tablename.substring(1, tablename.length() - 1).split("\\s+");
            tablename = split[0];

            nativeSql.append("insert into " + tablename.toUpperCase() + " VALUES (");

            for (int j = 0; j < strings.length; j++) {
                if (j != strings.length - 1) {
                    nativeSql.append("'" + strings[j] + "',");
                } else {
                    nativeSql.append("'" + strings[strings.length - 1] + "');");
                }
            }
            Query nativeQuery = em.createNativeQuery(nativeSql.toString());
            nativeQuery.executeUpdate();
        }
        tableindex=0;
        valueindex=0;
    }

    @Override
    public void Dtsave(HashMap<String, List<String>> read, String type, String cityname,String filename) {
        int valueindex = 0;
        int tableindex = 0;
        int index = 0;
        int createindex = 0;
        String tablename = null;
        String filed = null;
        String name = null;
        String citynames = null;

        List<String> values = read.get("value");

        if (type.equals("DT")) {
            if (cityname.equals("gl")) {
                citynames = "果洛";
                name = "GUOLUO_DT";
            } else if (cityname.equals("hb")) {
                citynames = "海北";
                name = "HAIBEI_DT";
            } else if (cityname.equals("hd")) {
                citynames = "海东";
                name = "HAIDONG_DT";
            } else if (cityname.equals("hh")) {
                citynames = "黄化";
                name = "HUANGHUA_DT";
            } else if (cityname.equals("hnz")) {
                citynames = "海南";
                name = "HAINAN_DT";
            } else if (cityname.equals("xn")) {
                citynames = "西宁";
                name = "XINING_DT";
            } else if (cityname.equals("hx")) {
                citynames = "海西";
                name = "HAIXI_DT";
            }
        }

        for (int i = 0; i<values.size(); i++) {
            StringBuffer buffer = new StringBuffer();
            StringBuffer filedSql = new StringBuffer();
            String[] value = null;
            String[] fields = null;
            String time = null;

            StringBuffer nativeSql = new StringBuffer();
            String s = values.get(i);

            String[] split1 = filename.split("_");
            if (type.equals("CIME")) {
                time=split1[2];
            }else {
                time=split1[1];
            }

            if (s.contains("#")) {
                value = s.split("\\s+");
            }else if (s.contains("@")) {
                fields = s.split("\\s+");
            }

            if (s.contains("<")) {
                if (!s.contains("/")) {
                    tablename=s;
                    tablename = tablename.substring(1,tablename.length()-1);
                }else {
                    index=0;
                    createindex=0;
                }
            }

            //查询表中是否有该表
            List tablelist = jobMapper.findTable(name,tablename.toUpperCase());

            if (index==0 && tablelist.size()==0 && fields!=null) {
                logger.info("创建表:" + tablename);

                String createTableSQL = "CREATE TABLE "+name+"."+tablename.toUpperCase()+"(";
                buffer.append(createTableSQL);

                for (int a = 1; a < fields.length; a++) {
                    if (a!=fields.length-1) {
                        if (a==1) {
                            buffer.append("\"id\" INT IDENTITY(1, 1) NOT NULL,");
                        }
                        buffer.append("\""+fields[a]+"\" VARCHAR(200),");
                    } else {
                        buffer.append("\""+fields[a]+"\" VARCHAR(200),\"owner\" VARCHAR(200),\"f_time\" VARCHAR(200))");
                    }
                }
                //执行sql
                jdbcTemplate.execute(buffer.toString());
                index=1;
            }

            //字段拼接
            if (fields!=null) {
                for (int j = 1; j < fields.length; j++) {
                    if (j!=fields.length-1) {
                        filedSql.append("\""+fields[j]+"\",");
                    }else {
                        filedSql.append("\""+fields[j]+"\",\"f_time\"");
                        filed=filedSql.toString();
                    }
                }
                logger.info("表字段：:" +filed );
            }

            //插入语句
            if (value!=null) {
                nativeSql.append("insert into "+name+"."+ tablename.toUpperCase() + " ("+filed+")  VALUES (");
                for (int j = 1; j < value.length; j++) {
                    if (j==3) {
                        if (tablename.equals("ACLineSegment") && value[j].equals("0")) {
                            continue;
                        }
                    }
                    if (j==6 && tablename.equals("ACLineSegment") && value.length==12) {
                        nativeSql.append("'0','" + value[j] + "',");
                    }

                    if (j != value.length - 1) {
                        nativeSql.append("'" + value[j] + "',");
                    } else {
                        nativeSql.append("'" + value[value.length - 1] + "','"+time+"');");
                    }
                }
                jdbcTemplate.execute(nativeSql.toString());
            }
        }
    }

    public void saveFileName(String filename) {
        //获取时间
        Date date = new Date();
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
        String time = simpleDateFormat.format(date);

        jobMapper.saveFileName(filename,time);
    }

    public void saveErrorFileName(String filename) {
        //获取时间
        Date date = new Date();
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
        String time = simpleDateFormat.format(date);

        jobMapper.saveErrorFileName(filename,time);
    }

    @Override
    public List<String> findFileName(String filename) {
        return jobMapper.findFileName(filename);
    }
}
